package com.abc.recycling.mapper;

import com.abc.recycling.model.RecyclingStation;
import org.apache.ibatis.annotations.*;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Mapper
public interface StationMapper {
    
    @Select("SELECT * FROM recycling_station WHERE id = #{id}")
    RecyclingStation findById(@Param("id") Long id);
    
    @Select("SELECT * FROM recycling_station WHERE status = 1")
    List<RecyclingStation> findAllActive();
    
    @Select("SELECT * FROM recycling_station")
    List<RecyclingStation> findAll();
    
    @Insert("INSERT INTO recycling_station(name, address, description, latitude, longitude, " +
            "contact_phone, contact_person, business_hours, accepted_items, status, " +
            "create_time, update_time) " +
            "VALUES(#{name}, #{address}, #{description}, #{latitude}, #{longitude}, " +
            "#{contactPhone}, #{contactPerson}, #{businessHours}, #{acceptedItems}, #{status}, " +
            "#{createTime}, #{updateTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insert(RecyclingStation station);
    
    @Update("UPDATE recycling_station SET name = #{name}, address = #{address}, " +
            "description = #{description}, latitude = #{latitude}, longitude = #{longitude}, " +
            "contact_phone = #{contactPhone}, contact_person = #{contactPerson}, " +
            "business_hours = #{businessHours}, accepted_items = #{acceptedItems}, " +
            "status = #{status}, update_time = #{updateTime} WHERE id = #{id}")
    void update(RecyclingStation station);
    
    @Delete("DELETE FROM recycling_station WHERE id = #{id}")
    void deleteById(@Param("id") Long id);
    
    @Select("SELECT COUNT(*) FROM recycling_station WHERE status = 1")
    Long countActiveStations();

    @Select("SELECT COUNT(*) FROM recycling_station")
    Long countTotalStations();

    @Select("SELECT s.*, COUNT(o.id) as order_count " +
            "FROM recycling_station s " +
            "LEFT JOIN recycling_order o ON s.id = o.station_id " +
            "WHERE s.status = 1 " +
            "GROUP BY s.id " +
            "ORDER BY order_count DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getTopStations(@Param("limit") int limit);

    @Select("SELECT region, COUNT(*) as count " +
            "FROM recycling_station " +
            "WHERE status = 1 " +
            "GROUP BY region " +
            "ORDER BY count DESC")
    List<Map<String, Object>> getStationDistributionByRegion();

    @Select("SELECT COUNT(*) FROM recycling_station " +
            "WHERE create_time BETWEEN #{startTime} AND #{endTime}")
    Long countStationsByDateRange(@Param("startTime") LocalDateTime startTime,
                                @Param("endTime") LocalDateTime endTime);

    @Select("SELECT s.*, " +
            "COUNT(o.id) as order_count, " +
            "COALESCE(SUM(o.actual_weight), 0) as total_weight, " +
            "COALESCE(SUM(o.price), 0) as total_income " +
            "FROM recycling_station s " +
            "LEFT JOIN recycling_order o ON s.id = o.station_id " +
            "WHERE s.id = #{stationId} " +
            "AND o.create_time BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY s.id")
    Map<String, Object> getStationStats(@Param("stationId") Long stationId,
                                      @Param("startTime") LocalDateTime startTime,
                                      @Param("endTime") LocalDateTime endTime);

    @Select("SELECT DATE_FORMAT(o.create_time, '%Y-%m-%d') as date, " +
            "COUNT(*) as order_count, " +
            "COALESCE(SUM(o.actual_weight), 0) as total_weight, " +
            "COALESCE(SUM(o.price), 0) as total_income " +
            "FROM recycling_station s " +
            "LEFT JOIN recycling_order o ON s.id = o.station_id " +
            "WHERE s.id = #{stationId} " +
            "AND o.create_time BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY DATE_FORMAT(o.create_time, '%Y-%m-%d') " +
            "ORDER BY date DESC")
    List<Map<String, Object>> getStationDailyStats(@Param("stationId") Long stationId,
                                                  @Param("startTime") LocalDateTime startTime,
                                                  @Param("endTime") LocalDateTime endTime);

    @Select("SELECT item_type, COUNT(*) as count " +
            "FROM recycling_station s " +
            "JOIN recycling_order o ON s.id = o.station_id " +
            "WHERE s.id = #{stationId} " +
            "AND o.create_time BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY item_type " +
            "ORDER BY count DESC")
    List<Map<String, Object>> getStationItemDistribution(@Param("stationId") Long stationId,
                                                        @Param("startTime") LocalDateTime startTime,
                                                        @Param("endTime") LocalDateTime endTime);

    Long countTotal();
    Long countByRegion(@Param("region") String region);
} 